Webcast: Trends in Network Access--Take a look at the latest trends in this fast-moving market and get guidance on your best current bets for maintaining endpoint security on your network.

Whitepaper: Barclays Capital Achieves Guaranteed Reliability and Performance with New Fixed-Income Solution Barclays Capital updated its existing trade management system (TMS) system with a solution based on Microsoft SQL Server 2005. Today, database queries no longer slow position updates, scalable solutions support anticipated growth, performance of Web services is up by 30%, and they can process more trades per second. » Whitepaper: Xerox Develops Scalable, Hosted Solution to Optimize Global Print Fleet Management Read how Xerox made its Office Services' "print fleet" management solutions easier and more cost effective using Microsoft SQL Server 2005 Enterprise Edition. This resulted in increased developer productivity; high performance, availability, and scalability; and new levels of customer satisfaction. » Whitepaper: Hyundai Aims to Boost Quality and Reduce Costs with SQL Server 2005 Data Warehouse Hyundai wanted to increase vehicle quality and customer satisfaction while reducing warranty costs. They built a business intelligence data warehouse hosted on Microsoft SQL Server 2005, resulting in the agility to identify problems faster, enhanced quality through use of non-warranty data, easier regulatory compliance, and lower TCO. » Whitepaper: Mediterranean Shipping Company Processes 5 Terabytes of Data with SQL Server 2005 Mediterranean Shipping Company needed an enterprise solution that would scale with growth and simplify database administration. Find out how they increased agility to swiftly respond to customer needs, simplified database administration, and provided faster query responses by upgrading to Microsoft. SQL Server 2005. » Whitepaper: Bookseller Gains Business Insights Across Sales Channels with New Data Warehouse Barnes & Noble decided to deploy a new data warehouse using Microsoft SQL Server 2005. Today, daily transactional information is loaded faster, reports are available to managers quickly for more insightful business decisions, and the company has a solution that is improving profitability and enhancing relationships with customers.»

September 16, 2005
Auto Logout Users for DB Maintenance
By Danny Lesandrini

One thing Access developers love about using SQL Server as the back end is that it is easy to do maintenance.  I can't tell you how many times I toured around an office, looking for users who had their client open and connected to the data so I could ask them to log out.  Too many times, the offender was at lunch or away from their desks, with their desktops locked.

After suffering enough frustration, I searched for a solution to that problem and developed a few simple components that may be added to any Access database.  All of the necessary code is included in the download for this article.  Below, I will outline the process.

The Plan:

There is probably no gentle way to "kick" users out of an application, but when it has to be done, it has to be done.  The idea behind this process is ...

 The countdown form I came up with is shown below.  I like the yellow and red diagonal stripes because they cannot be mistaken for anything but the gravest of warnings.  If the user has gone home, they will never actually see this dialog, but if emergency maintenance ever needs to be done during business hours, this image should provide sufficient warning and time for users to exit the application gracefully.

You may test the application of this process by running the Auto-Logout.mdb file that is included in the download.  The demo form points to the table that contains a LogoutAllUsers field, which serves as the flag to trip the logout.  You may experiment by toggling the switch on and off and see the warning appear and disappear, respectively.  I keep this data in my "version" table, along with other pieces of data that have one distinct value, as this table is allowed to have only one row.  It is either version 1.0.0 or it is not.  Users must either be logged out, or they may not.  As we will see, being assured of only a single such record simplifies the code somewhat.

Ever so little code required...

Your startup form should include a simple call to open the Auto-Logout form in hidden mode.  That's going to look like this ...

DoCmd.OpenForm "frmLogoutTimer", , , , , acHidden

The form, frmLogoutTimer, contains the following code on its Timer event.  Note that the default interval is shown below as 30,000, which amounts to 10 seconds.  This value can be increased so that the timer is not firing so often.  Remember that Interval of 3000 equates roughly to a second, so you would have to set the Interval property to 180,000 for a minute and 900,000 for 5 minutes.

After checking the flag, this code either increases the interval and opens the form, or closes the form and decreases the interval back to the default.  That is really all there is to this piece.  As a hidden form, no one ever really sees it, though in the image above I have made it visible for the purpose of the demo.

Private Sub Form_Timer()
   On Error GoTo Err_Handler
   Dim fLogout As Boolean
   
   fLogout = DLookup("[LogOutAllUsers]", "[tblVersionServer]")
   If fLogout = True Then
      Me.TimerInterval = 180000
      DoCmd.OpenForm "frmLogoutStatus"
   Else
      Me.TimerInterval = 30000
      If IsLoaded("frmLogoutStatus") Then
         DoCmd.Close acForm, "frmLogoutStatus"
      End If
   End If
   
Exit_Here:
   Exit Sub
Err_Handler:
   MsgBox Err.Description, vbExclamation, "Error"
   Resume Exit_Here
End Sub

Once the form, frmLogoutStatus, is loaded, the countdown begins.  It is a simple date-time comparison with a static variable that is set when the form opens.  When the time reaches zero (or less than zero ... remember the timer is not completely reliable) then the DoCmd.Quit method is called and the application closes. (I have removed error handlers to simplify the code.)

Private mdat_StartCountdownTime As Date
Private Sub Form_Open(iCancel As Integer)
   mdat_StartCountdownTime = DateAdd("n", 3, Now())
   Me!txtMinsSecs = " 3  minutes and  0  seconds "
End Sub
Private Sub Form_Timer()
   Dim intIMins As Integer
   Dim intISecs As Integer
   Dim fLogout As Boolean
   
   fLogout = DLookup("[LogOutAllUsers]", "[tblVersionServer]")
   If fLogout = False Then DoCmd.Close acForm, Me.Name
   
   intIMins = DateDiff("s", Now(), mdat_StartCountdownTime) \ 60
   intISecs = DateDiff("s", Now(), DateAdd("n", (intIMins * -1), mdat_StartCountdownTime))
   
   If intIMins = 2 And intISecs = 0 Then
      Me.Visible = True
   End If
   If intIMins = 1 And intISecs = 0 Then
      Me.Visible = True
   End If
   If intIMins = 0 And intISecs = 20 Then
      Me.Visible = True
   End If
   
   If intIMins <= 0 And intISecs <= 0 Then
      DoCmd.Quit
   Else
      On Error Resume Next
      Me.txtMinsSecs = " " & intIMins & "  minutes and  " & intISecs & "  seconds "
   End If
End Sub

Notice that if the logout flag was turned off, that is, the admin no longer wants to log users out, then the form is closed and the Quit command is never reached.  Note too that if the user hides the form, it is made visible every minute until the application is closed, to remind the user that time is running out.  (That is why the interval is increased above from 10 seconds to 60 seconds when the flag is true.)

The last order of business is to update the form so users can see how much time they have left.  Using the minute and second calculations, a message string is generated and applied to the control source of the display control.  I did not do anything fancy here to truncate the "s" on minutes when the value is one.  That would be graceful, but frankly, I never cared enough about the verbiage of this disposable dialog to change it.

Custom Timing

Everything you need to create an auto logout routine is included in the download for this article except one thing:  your personal touch.  On some older machines, users can detect a slowdown when the timer goes off, so a 10-second interval might be irritating.  You could up the value to 30 minutes, which would reduce user interference but require a longer wait to get at the back end for maintenance.  In addition, the countdown code is tooled for 3 minutes but a few simple modifications could lengthen or shorten that time as well.  Personally, I like the times as I have set them, but you will need to decide what works for your users, your network and your database admin.

» See All Articles by Columnist Danny J. Lesandrini

June 28th Webcast: Ensuring Your Ability to Recover from a Disaster--Register now!
Stay Out of the Headlines--Sign Up for this Storage Security Webcast
Why virtualize? Find out! Read this whitepaper: Approaching a Virtualized IT World With Confidence
Webcast: Explore virtualization technology and its applicability in both SAN and NAS architectures.
Enhance your Web site with the Dynamic HTML HierMenus Code